<?php
/**
 * Created by PhpStorm.
 * User: yangliu
 * Date: 2018/12/17
 * Time: 9:57 AM
 */

namespace fast;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;


class Office
{
    /**
     * 导出excel表
     * $data：要导出excel表的数据，接受一个二维数组
     * $name：excel表的表名
     * $head：excel表的表头，接受一个一维数组
     * $key：$data中对应表头的键的数组，接受一个一维数组
     * 备注：此函数缺点是，表头（对应列数）不能超过26；
     *循环不够灵活，一个单元格中不方便存放两个数据库字段的值
     */
    public function outdata($name='测试表', $data=[], $head=[], $keys=[],$sheelTite=[])
    {
        $count = count($head);  //计算表头数量

        $spreadsheet = new Spreadsheet();
        $styleArray = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
            ],];

        /*--------------开始从数据库提取信息插入Excel表中------------------*/
        for ($s = 0; $s <= count($data) -1 ; $s++)
        {
            if($s !== 0) $spreadsheet->createSheet(); //创建一个sheel
            $spreadsheet->getActiveSheet()->getRowDimension('1')->setRowHeight(30);//设置行高25
            $spreadsheet->getActiveSheet()->getRowDimension('2')->setRowHeight(25);//设置行高25

            //设置样式
            $sheet = $spreadsheet->setActiveSheetIndex($s);
            $sheet->getStyle('A1')->applyFromArray($styleArray)->getFont()->setSize(23);
            $sheet->getStyle('A2:'.strtoupper(chr($count+64)).'2')->applyFromArray($styleArray)->getFont()->setSize(12);
            $sheet->mergeCells('A1:'.strtoupper(chr($count+64)).'1');
            $sheet->setCellValue('A1',$sheelTite[$s].$name);//第一行标题

            for ($i = 65; $i < $count + 65; $i++) {     //数字转字母从65开始，循环设置表头：
                $sheet->setCellValue(strtoupper(chr($i)) . '2', $head[$i - 65]);
                $sheet->getStyle(strtoupper(chr($i)) .'2')->applyFromArray($styleArray);//设置为水平居中对齐
            }

            foreach ($data[$s] as $key => $item) {
                $item['id'] = $key+1;
                //循环设置单元格：
                //$key+2,因为第一行是表头，所以写到表格时   从第二行开始写
                for ($i = 65; $i < $count + 65; $i++) {     //数字转字母从65开始：
                    $sheet->setCellValue(strtoupper(chr($i)) . ($key + 3), $item[$keys[$i - 65]]);//设置值
                    $sheet->getStyle(strtoupper(chr($i)) . ($key + 3))->applyFromArray($styleArray);//设置为水平居中对齐
                    $spreadsheet->getActiveSheet()->getColumnDimension(strtoupper(chr($i)))->setWidth(20); //固定列宽
                }

//                $sheet->getStyle('C' . ($key + 5))->applyFromArray($styleArray)->getFont()->setSize(23);
//                $spreadsheet->getActiveSheet()->getStyle('A' . ($key + 5).':H'. ($key + 5))->getFill()
//                ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
//                ->getStartColor()->setARGB('D0D0D0'); //设置背景颜色

                $spreadsheet->getActiveSheet()->setTitle($sheelTite[$s]);
            }
            $sheet->setCellValue('A' . ($key + 5),"统计");
            for ($i = 67; $i < $count +65; $i++)
            {
                $string = strtoupper(chr($i));
                $sheet->setCellValue($string . ($key + 5),"=SUM({$string}3:{$string}".($key+5).")");
                $sheet->getStyle($string . ($key + 5))->applyFromArray($styleArray);//设置为水平居中对齐
            }
        }


        $spreadsheet->setactivesheetindex(0);//设置打开的默认shell
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $name . '.xlsx"');
        header('Cache-Control: max-age=0');
        $writer = new Xlsx($spreadsheet);
        $writer->save('php://output');

        //删除清空：
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        exit;
    }

    public function count_rank_execl($name='测试表', $data=[], $head=[], $keys=[])
    {
        $count = count($head);  //计算表头数量

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        for ($i = 65; $i < $count + 65; $i++) {     //数字转字母从65开始，循环设置表头：
            $sheet->setCellValue(strtoupper(chr($i)) . '1', $head[$i - 65]);
        }

        /*--------------开始从数据库提取信息插入Excel表中------------------*/


        foreach ($data as $key => $item) {             //循环设置单元格：
            //$key+2,因为第一行是表头，所以写到表格时   从第二行开始写

            for ($i = 65; $i < $count + 65; $i++) {     //数字转字母从65开始：
                $sheet->setCellValue(strtoupper(chr($i)) . ($key + 2), $item[$keys[$i - 65]]);
                $spreadsheet->getActiveSheet()->getColumnDimension(strtoupper(chr($i)))->setWidth(20); //固定列宽
            }

        }

        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $name . '.xlsx"');
        header('Cache-Control: max-age=0');
        $writer = new Xlsx($spreadsheet);
        $writer->save('php://output');

        //删除清空：
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        exit;
    }


}